using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.IO;
using System.Windows.Forms;

namespace GhostLogger
{
    public class DBConnection
    {
        #region Variables      
        
        static OleDbConnection con;
        static string conString;
        #endregion


        #region DB Basic

        /// <summary>
        /// Prepare the connection string for the DB
        /// </summary>
        /// <param name="strDatasource"></param>
        /// <param name="strUserName"></param>
        /// <param name="strPassword"></param>
        public static void PrepareConnectString(string strDatasource, string strUserName, string strPassword)
        {
            conString = "provider=MSDAORA;data source=" + strDatasource + ";user id=" + strUserName
                                                                                    + ";password=" + strPassword;

            /*string strCompleteDatasource = "(DESCRIPTION = "+
                                                "(ADDRESS_LIST = " +
                                                    "(ADDRESS = (PROTOCOL = TCP)(HOST = " + strHost + " )(PORT = " + strPort + ")) " +
                                                    ")(CONNECT_DATA = (SERVICE_NAME = " + strDatasource + ") ) )";

            conString = "provider=MSDAORA;data source=" + strCompleteDatasource + ";user id=" + strUserName + ";password=" + strPassword;            
            */
        }

        /// <summary>
        /// Connect to the DB.
        /// </summary>
        /// <returns></returns>
        public static bool Connect()
        {
            con = new OleDbConnection(conString);
            try
            {
                con.Open();
                return true;
            }
            catch (Exception)
            {
                return false;
            }

        }

        /// <summary>
        /// Close DB Connection
        /// </summary>
        public static void Close()
        {
            if ((con != null) && con.State == ConnectionState.Open)
                con.Close();
        }


        #endregion


        #region Installation

        /// <summary>
        /// Check Ghost Logger database packages and tables are correctly installed.
        /// </summary>
        /// <returns></returns>
        public static bool CheckInstallation()
        {

            bool tableExists = false;
            bool packagesExist = false;
            OleDbDataReader dataReader;
            try
            {

                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                OleDbCommand command = con.CreateCommand();
                command.CommandText = "SELECT count(*) tblCount FROM user_tables where table_name='C_DEBUG_CALL_STACK_TAB' or table_name = 'C_DEBUG_HISTORY_TAB'";
                dataReader = command.ExecuteReader();
                dataReader.Read();
                if (dataReader["tblCount"].ToString() == "2")
                {
                    tableExists = true;
                }
                dataReader.Close();

                command.CommandText = "select count( object_name) objcount " +
                                        " from user_objects " +
                                        " where object_name in ('C_DEBUG_CALL_STACK_API' , 'C_PLSQL_PARSER_API', 'C_PLSQL_LOGGER_API', 'C_DEBUG_TRACE_UTIL_API')";

                dataReader = command.ExecuteReader();
                dataReader.Read();
                if (dataReader["objcount"].ToString() == "8")
                {
                    packagesExist = true;
                }
                dataReader.Close();

                if (tableExists && packagesExist)
                {
                    return true;
                }


                return false;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                dataReader = null;
                con.Close();
            }

        }


        /// <summary>
        /// Install database tables and packages to the specidied database. 
        /// </summary>
        /// <param name="strDatasource"></param>
        /// <param name="strUserName"></param>
        /// <param name="strPassword"></param>
        public static void Install(string strDatasource, string strUserName, string strPassword)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                String fullpath = Application.ExecutablePath;
                String oraVersion;
                int oraGVersion = 0;



                FileInfo fullPathFile = new FileInfo(fullpath);
                fullpath = fullPathFile.DirectoryName;


                oraVersion = con.ServerVersion;
                char[] delimiters = new char[] { '.' };
                String[] temp;
                temp = oraVersion.Split(delimiters);

                DirectoryInfo dir = new DirectoryInfo(fullpath);
                Int32.TryParse(temp[0], out oraGVersion);
                if (oraGVersion < 10)
                {
                    MessageBox.Show(("Ghost Logger does not support Oracle " + oraVersion + "."), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    Application.Exit();
                    return;
                }
                else if (oraGVersion == 10)
                {
                    dir = new DirectoryInfo(fullpath + @"/database/10g");
                }
                else if (oraGVersion == 11)
                {
                    dir = new DirectoryInfo(fullpath + @"/database/11g");
                }



                StringBuilder fileNames = new StringBuilder();
                fileNames.AppendLine("SET feedback off");
                fileNames.AppendLine("SET verify off");
                fileNames.AppendLine("");

                FileInfo[] listFiles;

                listFiles = dir.GetFiles("*.cdb");
                fileNames.AppendLine(GetFilesInDirectory(listFiles));
                listFiles = dir.GetFiles("*.api");
                fileNames.AppendLine(GetFilesInDirectory(listFiles));
                listFiles = dir.GetFiles("*.apy");
                fileNames.AppendLine(GetFilesInDirectory(listFiles));

                fileNames.AppendLine("exit 0");

                File.WriteAllText(fullpath + @"/InstallLogger.tem", fileNames.ToString());


                String batContent = "sqlplus " + strUserName + "/" + strPassword + "@" + strDatasource + " @\"" + fullpath + "\"/InstallLogger.tem";
                batContent = batContent + "\n exit";
                File.WriteAllText(fullpath + @"/Install.bat", batContent);

                System.Diagnostics.Process rfp = new System.Diagnostics.Process();
                rfp = System.Diagnostics.Process.Start("Install.bat");
                rfp.WaitForExit();


            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Private method to get a list of all files in a folder. (This is to deploy all API, APY and CDB files in the location)
        /// </summary>
        /// <param name="listFiles"></param>
        /// <returns></returns>
        private static string GetFilesInDirectory(FileInfo[] listFiles)
        {
            StringBuilder sbRetValue = new StringBuilder();

            foreach (FileInfo file in listFiles)
            {
                sbRetValue.AppendLine("PROMPT Executing " + file.Name);
                sbRetValue.AppendLine("START \"" + file.FullName + "\"");
            }
            return sbRetValue.ToString();
        }

        /// <summary>
        /// Read file from the install location in order to deploy it to database.
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private static string ReadFile(string fileName)
        {
            StringBuilder retValue = new StringBuilder();
            try
            {
                //retValue = File.ReadAllText(fileName);
                //File.

                using (StreamReader reader = new StreamReader(fileName))
                {
                    string line;
                    while ((line = reader.ReadLine()) != null)
                    {
                        retValue.Append(line);
                        retValue.Append(" || chr(10) || ");
                    }

                }

                return retValue.ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
        }



        #endregion


        #region Get Package Information

        /// <summary>
        /// Get list of all VALID packages in the database.
        /// </summary>
        /// <returns></returns>
        public static DataSet RetrievePackages()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                //string strSql = "SELECT object_name from user_objects t where t.OBJECT_TYPE = 'PACKAGE'";
                string strSql = "SELECT p.package_name, p.lu_name, l.module " +
                                    " FROM dictionary_sys_lu l, dictionary_sys_package p, user_objects u " +
                                    " WHERE l.lu_name = p.lu_name " +
                                    " AND u.OBJECT_NAME = p.package_name " +
                                    " AND u.OBJECT_TYPE = 'PACKAGE BODY' " +
                                    " AND u.status = 'VALID'";

                OleDbDataAdapter adpPackages = new OleDbDataAdapter(strSql, con);
                DataSet dsPackages = new DataSet();

                adpPackages.Fill(dsPackages, "dictionary_sys_package");

                return dsPackages;


            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Get the number of Invlaid packages in the database. 
        /// </summary>
        /// <returns></returns>
        public static int GetInvalidPackageCount()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                OleDbCommand command = con.CreateCommand();
                OleDbDataReader dataReader;
                String strNoOfInvalids;
                int retValue = 0;

                string strSql = @"select count(1) INVALIDS  from sys.user_objects t " +
                                    " where t.status != 'VALID'";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strNoOfInvalids = dataReader["INVALIDS"].ToString();
                    retValue = Int32.Parse(strNoOfInvalids);
                }
                dataReader.Close();

                return retValue;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }
        }

        #endregion


        #region Compile

        /// <summary>
        /// Compile database package with the addition of debug method calls. (This takes time)
        /// </summary>
        /// <param name="packageName"></param>
        public static void Compile(string packageName)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "declare  " +
                                    " begin " +
                                    " c_plsql_logger_api.Parse_Package('" +
                                    packageName + "');" +
                                    " end;";
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;

                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                //Execute Immdiate in Ora11g throws exception but not in 10G
                //throw e;
            }
            finally
            {
                con.Close();
            }

        }


        /// <summary>
        /// Remove debug information from the specified package.
        /// </summary>
        /// <param name="packageName"></param>
        public static void Recompile(string packageName)
        {
            bool bolRecomplie = false;
            try
            {
                if (CheckPackageVersion(packageName))
                {
                    bolRecomplie = true;
                }
                else
                {
                    if (MessageBox.Show("A new version of " + packageName + " is available in the database. Do you want to deploy an older version anyway?", "Newer Version", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Exclamation) == DialogResult.Yes)
                    {
                        bolRecomplie = true;
                    }
                }

                if (bolRecomplie)
                {
                    if (con.State != ConnectionState.Open)
                    {
                        Connect();
                    }

                    string strSql = "declare  " +
                                        " begin " +
                                        " c_debug_trace_util_api.Remove_History('" +
                                        packageName + "');" +
                                        " end;";
                    OleDbCommand cmd = con.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSql;

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }

        /// <summary>
        /// Recompile the packages to remove PRAGMA violation issues. 
        /// </summary>
        /// <param name="packageName"></param>
        public static void RecompilePragmaErrors(string packageName)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "declare  " +
                                    " begin " +
                                    " c_plsql_logger_api.Parse_Pragma_Error('" +
                                    packageName + "');" +
                                    " end;";
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;

                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }

        #endregion


        #region Debug Data

        ///<summary>
        ///Get All Saved debug information from the database. 
        ///</summary>
        ///<returns></returns>
        public static DataSet RetrieveDebugInfo()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "SELECT * FROM c_debug_call_stack_tab order by exec_id";

                OleDbDataAdapter adpPackages = new OleDbDataAdapter(strSql, con);
                DataSet dsPackages = new DataSet();

                adpPackages.Fill(dsPackages, "c_debug_call_stack_tab");
                return dsPackages;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        
        /// <summary>
        /// Query saved debug infomation from the database. (Client not complete)
        /// </summary>
        /// <param name="moduleId"></param>
        /// <param name="package"></param>
        /// <param name="userId"></param>
        /// <param name="method"></param>
        /// <param name="execId"></param>
        /// <returns></returns>
        public static DataSet RetrieveDebugInfo(string moduleId, string package, string userId, string method, string execId)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "SELECT * FROM c_debug_call_stack_tab ";
                string strWhere = null;

                if (moduleId != String.Empty)
                {
                    if (strWhere == null)
                        strWhere = "WHERE MODULE LIKE '" + moduleId.ToUpper() + "'";
                    else
                        strWhere += "AND MODULE LIKE '" + moduleId.ToUpper() + "'";
                }

                if (package != String.Empty)
                {
                    if (strWhere == null)
                        strWhere = "WHERE PACKAGE_NAME LIKE '" + package.ToUpper() + "'";
                    else
                        strWhere += "AND PACKAGE_NAME LIKE '" + package.ToUpper() + "'";
                }

                if (userId != String.Empty)
                {
                    if (strWhere == null)
                        strWhere = "WHERE USER_ID LIKE '" + userId.ToUpper() + "'";
                    else
                        strWhere += "AND USER_ID LIKE '" + userId.ToUpper() + "'";
                }

                if (method != String.Empty)
                {
                    if (strWhere == null)
                        strWhere = "WHERE METHOD_NAME LIKE '" + method + "'";
                    else
                        strWhere += "AND METHOD_NAME LIKE '" + method + "'";
                }


                OleDbDataAdapter adpPackages = new OleDbDataAdapter((strSql + strWhere), con);
                DataSet dsPackages = new DataSet();

                adpPackages.Fill(dsPackages, "c_debug_call_stack_tab");
                return dsPackages;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Get information on arguments of a single method call.
        /// </summary>
        /// <param name="packageName"></param>
        /// <param name="methodName"></param>
        /// <returns></returns>
        public static ArrayList RetrieveArgumentInfo(string packageName, string methodName)
        {
            OleDbDataReader dataReader;
            ArrayList retValue = new ArrayList();
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "SELECT OBJECT_ID " +
                                    " from user_objects " +
                                    " where OBJECT_TYPE='PACKAGE' and OBJECT_NAME = '" +
                                    packageName +
                                    "' ";

                OleDbCommand command = con.CreateCommand();
                command.CommandText = strSql;
                dataReader = command.ExecuteReader();
                dataReader.Read();

                string strPackageId = dataReader["OBJECT_ID"].ToString();
                dataReader.Close();

                strSql = "select ARGUMENT_NAME from user_arguments where OBJECT_ID = '" +
                            strPackageId + "' and OBJECT_NAME = '" +
                            methodName.ToUpper() + "'" +
                            " and DATA_LEVEL = 0 and ARGUMENT_NAME is not null";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    retValue.Add(dataReader["ARGUMENT_NAME"].ToString());
                }
                dataReader.Close();

                return retValue;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        
        /// <summary>
        /// Delete saved debug data.
        /// </summary>
        public static void DeleteAllData()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "DELETE FROM C_DEBUG_CALL_STACK_TAB";
                OleDbCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;

                cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }
        
        #endregion


        #region History

        /// <summary>
        /// Get History on packages which were compiled with Debug infomation.
        /// </summary>
        /// <returns></returns>
        public static DataSet RetrieveDebugAddedHistory()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                string strSql = "SELECT DISTINCT(package_name) from c_debug_history_tab";

                OleDbDataAdapter adpPackages = new OleDbDataAdapter(strSql, con);
                DataSet dsPackages = new DataSet();

                adpPackages.Fill(dsPackages, "c_debug_history_tab");
                return dsPackages;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        
        /// <summary>
        /// Check the version of the package before restoring the previous version
        /// </summary>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public static bool CheckPackageVersion(string packageName)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                OleDbCommand command = con.CreateCommand();
                OleDbDataReader dataReader;
                DateTime dateHistory = DateTime.MinValue;
                DateTime dateNow = DateTime.MinValue;
                String strDate;

                bool retValue = false;

                string strSql = "select OBJ_TIMESTAMP " +
                                    " from c_debug_history_tab  " +
                                    " where package_name = '" + packageName + "'";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strDate = dataReader["OBJ_TIMESTAMP"].ToString();
                    DateTime.TryParse(strDate, out dateHistory);
                }
                dataReader.Close();


                strSql = "SELECT to_date(t.TIMESTAMP,'YYYY-MM-DD:HH24:MI:SS') TIMESTAMP" +
                                    " FROM user_objects t " +
                                    " WHERE t.OBJECT_NAME = '" + packageName + "'" +
                                    " AND t.OBJECT_TYPE = 'PACKAGE BODY'";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strDate = dataReader["TIMESTAMP"].ToString();
                    DateTime.TryParse(strDate, out dateNow);
                }
                dataReader.Close();


                if (dateHistory.Equals(dateNow))
                {
                    return true;
                }
                return false;

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }
               
        #endregion


        #region Get Compiled Errors

        /// <summary>
        /// Get the status of the package after compiling with new Debug method calls.
        /// </summary>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public static bool GetPackageInvalid(string packageName)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                OleDbCommand command = con.CreateCommand();
                OleDbDataReader dataReader;
                String strInvalidPackage;
                bool retValue = false;

                string strSql = "select OBJECT_NAME from user_objects t where t.OBJECT_NAME = '"
                    + packageName
                    + "' and t.status = 'INVALID'";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strInvalidPackage = dataReader["OBJECT_NAME"].ToString();
                    retValue = true;
                }
                dataReader.Close();

                return retValue;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }


        /// <summary>
        /// Get the no of errors which were resulted from the PRAGMA issues. 
        /// </summary>
        /// <param name="packageName"></param>
        /// <returns></returns>
        public static int GetPackagePragmaErrorCount(string packageName)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }

                OleDbCommand command = con.CreateCommand();
                OleDbDataReader dataReader;
                String strNoOfErrors;
                int retValue = 0;

                string strSql = "select count(1) NO_OF_ERROR " +
                                    " from user_errors t " +
                                    " where t.name = '" +
                                    packageName
                                    + "' and t.message_number = 452";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strNoOfErrors = dataReader["NO_OF_ERROR"].ToString();
                    retValue = Int32.Parse(strNoOfErrors);
                }
                dataReader.Close();

                return retValue;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }      
       
        #endregion


        #region Obsolete
        /*public static string GetOracleVersion()
        {
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    Connect();
                }
                return con.ServerVersion;

                OleDbCommand command = con.CreateCommand();
                OleDbDataReader dataReader;
                String strVersion = "" ;
                int retValue = 0;

                string strSql = "SELECT VERSION FROM v$instance;";

                command.CommandText = strSql;
                dataReader = command.ExecuteReader();

                if (dataReader.Read())
                {
                    strVersion = dataReader["VERSION"].ToString();                    
                }
                dataReader.Close();

                return strVersion;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Close();
            }

        }
        */
        #endregion

    }
}
